import java.sql.{Connection, DriverManager}

import org.apache.spark.sql.SparkSession

/**
 * @author Xue ShuWen
 * @date 2022/12/10 20:24
 */
object ClassGenderNum {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("MysqlQueryDemo").master("local").getOrCreate()
    val jdbcDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://127.0.0.1:3306/stuInfo?useUnicode=true&characterEncoding=utf-8")
      .option("dbtable", "studentInfo")
      .option("user", "root")
      .option("password", "root")
      .load()
    jdbcDF.createOrReplaceTempView("studentInfo")

    val resultset = spark.sql("select classNo,sum(case when gender=1 then 1 else 0 end ),sum(case when gender='0' then 1 else 0 end ) from studentInfo group by classNo order by classNo;").collect()



    val url = "jdbc:mysql://localhost:3306/stuInfo?useUnicode=true&characterEncoding=utf-8&useSSL=false"
    val driver = "com.mysql.cj.jdbc.Driver"
    val username = "root"
    val password = "root"
    //初始化数据连接
    try {
      Class.forName(driver)
      //得到连接
      val connection: Connection = DriverManager.getConnection(url, username, password)
      val statement = connection.createStatement
      resultset.foreach(x=>{
        val sql = f"UPDATE genderNum set maleNum=${x(1)},femaleNum=${x(2)} where `classNo`=${x(0)};"
        //    执行更新操作
        val rs3 = statement.executeUpdate(sql)
      })
      println("更新数据完成！")
      connection.close
    } catch {
      case e: Exception => e.printStackTrace
    }
  }
}
